package com.opennetexam.manager.sqlprovider;

import freemarker.template.utility.StringUtil;
import org.apache.log4j.Logger;
import org.springframework.util.StringUtils;

import java.util.HashMap;
import java.util.Map;

/**
 * Created on 2017/11/14.
 */
public class ExamPhotoProvider {

    private static Logger logger = Logger.getLogger(ExamPhotoProvider.class);

    /**
     * 获取考生照片列表
     * @param parameter
     * @return
     */
    public static String getPhotoList(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String planId = (String) parameter.get("planId");
        String roomId = (String) parameter.get("roomId");
        String sceneId = (String) parameter.get("sceneId");
        String examineeName = (String) parameter.get("examineeName");
        String examCardNo = (String) parameter.get("examCardNo");
        Integer statusId = (Integer)parameter.get("statusId");


        stringBuffer.append("SELECT \n" +
                "DISTINCT e.ExamCardNo,\n" +
                "e.AppID,\n" +
                "e.ExamPlanInfoID,\n" +
                "e.ExamineeID,\n" +
                "e.ExamineeName,\n" +
                "e.Sex,\n" +
                "ee.Photo,\n" +
                "ee.PhotoResource,\n" +
                "es.ExamSceneID,\n" +
                "es.ExamSceneName,\n" +
                "er.ExamRoomID,\n" +
                "er.ExamRoomName,\n" +
                "es.PlanBeginTime \n" +
                "from ExamineeArrange ea  \n" +
                "LEFT JOIN Examinee e on ea.ExamineeID = e.ExamineeID \n" +
                "LEFT JOIN ExamScenes es on ea.ExamSceneID = es.ExamSceneID \n" +
                "LEFT JOIN ExamRoom er on ea.ExamRoomID = er.ExamRoomID \n" +
                "LEFT JOIN ExamineeExt ee on ee.ExamPlanInfoID = ea.ExamPlanInfoID and ea.AppID = ee.AppID and ea.ExamSceneID = ee.ExamSceneID and ea.ExamineeID = ee.ExamineeID  \n"+
                "where 1 = 1 "
                );
        stringBuffer.append(" AND ea.AppID = \'").append(appId).append("\'").append(" AND ea.ExamPlanInfoID = \'").append(planId).append("\' AND ea.ExamSceneID = \'").append(sceneId).append("\' ");
        if(roomId != null){
            stringBuffer.append(" AND ea.ExamRoomID = \'").append(roomId).append("\' ");
        }
        if(!StringUtils.isEmpty(examineeName)){
            stringBuffer.append(" AND e.ExamineeName like \'%").append(examineeName).append("%\'");
        }
        if(!StringUtils.isEmpty(examCardNo)){
            stringBuffer.append(" AND e.ExamCardNo like \'%").append(examCardNo).append("%\'");
        }
        if(statusId != null && statusId == 0){
            stringBuffer.append(" AND ISNULL(ee.Photo)  AND ISNULL(ee.PhotoResource) ");
        }else if(statusId != null && statusId == 1){
            stringBuffer.append(" AND NOT ISNULL(ee.Photo)  AND NOT ISNULL(ee.PhotoResource) ");
        }

        return stringBuffer.toString();
    }

    public static void main(String[] args){
        Map<String,Object> map = new HashMap<>();
        map.put("appId","1");
        map.put("planId",66);
        map.put("roomId",154);
        map.put("sceneId",21);
        map.put("examineeName","李明");
        map.put("examCardNo","CS731002");
        map.put("statusId",0);
        ExamPhotoProvider.getPhotoList(map);
    }
}
